﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Serialization;
using System.IO;
using System.EnterpriseServices;
using System.Text;
using Microsoft.Dynamics.GP.eConnect;
using Microsoft.Dynamics.GP.eConnect.MiscRoutines;
using Microsoft.Dynamics.GP.eConnect.Serialization;


namespace MobileSalesWS
{
    /// <summary>
    /// Summary description for Service1
    /// Reference Site: http://victoriayudin.com/gp-tables/
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    public class SalesDoc : System.Web.Services.WebService
    {
        #region :: Public Methods ::

        [WebMethod]
        public string Lookup(string loginID, string password, string docType, string partialDocID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);

                // Translate DocType from string to int
                docType = TranslateDocType(docType);

                // Lookup Customers
                string sqlQuery = "SELECT TOP 10 SOPTYPE, SOPNUMBE, CUSTNMBR, CUSTNAME, DOCDATE FROM SOP10100 ";
                sqlQuery += "WHERE SOPTYPE = " + docType + " AND SOPNUMBE LIKE '" + partialDocID.Trim() + "%' ";
                sqlQuery += "ORDER BY SOPNUMBE";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader salesDocDR = sqlCmd.ExecuteReader();
                DataTable salesDocDT = new DataTable("SalesDoc");
                salesDocDT.Load(salesDocDR);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                salesDocDT.WriteXml(xmlWriter);

                salesDocDR.Close();
                dbConn.Close();

                string salesDocListXML = xmlWriter.ToString();
                string salesDocListTransformedXML = Utils.TransformXML(salesDocListXML, "SalesDocList.xslt");

                return salesDocListTransformedXML;

                /*
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data/SampleXMLs");
                xmlPath += "\\" + "SalesDocList.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);
                
                return xmlDoc.OuterXml;
                 * */
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string Load(string loginID, string password, string docType, string docID)
        {
            try
            {
                // Translate DocType from string to int
                docType = TranslateDocType(docType);

                // Populate the eConnectOut object to specify the data request
                eConnectOut ecOut = new eConnectOut();
                ecOut.DOCTYPE = "Sales_Transaction";
                ecOut.OUTPUTTYPE = 2;
                ecOut.INDEX1FROM = docID;
                ecOut.INDEX1TO = docID;
                ecOut.INDEX2FROM = docType;
                ecOut.INDEX2TO = docType;
                ecOut.FORLIST = 1;

                // Create an XML document for the request
                RQeConnectOutType ecOutType = new RQeConnectOutType();
                ecOutType.eConnectOut = ecOut;
                eConnectType ecType = new eConnectType();
                ecType.RQeConnectOutType = new RQeConnectOutType[1];
                ecType.RQeConnectOutType[0] = ecOutType;

                StringBuilder ecReqSB = new StringBuilder();
                XmlTextWriter writer = new XmlTextWriter(new StringWriter(ecReqSB));
                writer.Formatting = Formatting.Indented;

                // Serialize the eConnect object using XMLWriter
                XmlSerializer ecSerializer = new XmlSerializer(ecType.GetType());
                ecSerializer.Serialize(writer, ecType);

                string connString = ConfigurationManager.ConnectionStrings["GPDatabase_ECONNECT"].ConnectionString;

                eConnectMethods ecMethods = new eConnectMethods();
                string ecRequestXML = ecReqSB.ToString();
                string ecResponseXML = ecMethods.eConnect_Requester(connString,
                    EnumTypes.ConnectionStringType.SqlClient, ecRequestXML);

                string salesDocTransformedXML = Utils.TransformXML(ecResponseXML, "SalesDoc.xslt");

                return salesDocTransformedXML;
                /*
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data/SampleXMLs");
                xmlPath += "\\" + "SalesDoc.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
                 * */
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public bool Save(string loginID, string password, string docXML)
        {
            try
            {
                // Save docXML to database via eConnect
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(docXML);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        [WebMethod]
        public string LookupSites(string loginID, string password, string partialSiteID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);

                // Lookup Sites
                string sqlQuery = "SELECT TOP 10 LOCNCODE, LOCNDSCR FROM IV40700 ";
                sqlQuery += "WHERE LOCNCODE LIKE '" + partialSiteID.Trim() + "%' ";
                sqlQuery += "ORDER BY LOCNCODE";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader siteDR = sqlCmd.ExecuteReader();
                DataTable siteDT = new DataTable("Site");
                siteDT.Load(siteDR);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                siteDT.WriteXml(xmlWriter);

                siteDR.Close();
                dbConn.Close();

                string siteListXML = xmlWriter.ToString();
                string siteListTransformedXML = Utils.TransformXML(siteListXML, "SiteList.xslt");

                return siteListTransformedXML;

                /*
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data/SampleXMLs");
                xmlPath += "\\" + "SiteList.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
                 * */
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string LookupCustomers(string loginID, string password, string partialCustomerID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);

                // Lookup Customers
                string sqlQuery = "SELECT TOP 10 CUSTNMBR, CUSTNAME FROM RM00101 ";
                sqlQuery += "WHERE CUSTNMBR LIKE '" + partialCustomerID.Trim() + "%' ";
                sqlQuery += "ORDER BY CUSTNMBR";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader customerDR = sqlCmd.ExecuteReader();
                DataTable customerDT = new DataTable("Customer");
                customerDT.Load(customerDR);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                customerDT.WriteXml(xmlWriter);

                customerDR.Close();
                dbConn.Close();

                string customerListXML = xmlWriter.ToString();
                string customerListTransformedXML = Utils.TransformXML(customerListXML, "CustomerList.xslt");

                return customerListTransformedXML;

                /*
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data/SampleXMLs");
                xmlPath += "\\" + "CustomerList.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
                 * */
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string LookupCustomerAddresses(string loginID, string password, string customerID, string partialAddressID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);

                // Lookup Customer Addresses
                string sqlQuery = "SELECT TOP 10 CUSTNMBR, ADRSCODE, CNTCPRSN, ADDRESS1, ADDRESS2, CITY, [STATE], ZIP, COUNTRY FROM RM00102 ";
                sqlQuery += "WHERE CUSTNMBR = '" + customerID.Trim() + "' AND ADRSCODE LIKE '" + partialAddressID.Trim() + "%' ";
                sqlQuery += "ORDER BY ADRSCODE";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader customerAddressDR = sqlCmd.ExecuteReader();
                DataTable customerAddressDT = new DataTable("CustomerAddress");
                customerAddressDT.Load(customerAddressDR);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                customerAddressDT.WriteXml(xmlWriter);

                customerAddressDR.Close();
                dbConn.Close();

                string customerAddressListXML = xmlWriter.ToString();
                string customerAddressListTransformedXML = Utils.TransformXML(customerAddressListXML, "CustomerAddressList.xslt");

                return customerAddressListTransformedXML;
                /*
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data/SampleXMLs");
                xmlPath += "\\" + "CustomerAddressList.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
                 * */
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        // Method Name: LookupItems
        // Assigned To: David Liman
        // Query: SELECT a.ITEMNMBR, a.ITEMDESC, a.ITMSHNAM, a.SELNGUOM, b.QTYONHND, c.LISTPRCE 
        // FROM IV00101 a 
        // INNER JOIN IV00102 b ON a.ITEMNMBR = b.ITEMNMBR 
        // INNER JOIN IV00105 c ON a.ITEMNMBR = c.ITEMNMBR
        // WHERE b.RCRDTYPE = 1 AND a.ITEMNMBR = <ItemID>
        [WebMethod]
        public string LookupItems(string loginID, string password, string partialItemID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);

                string sqlQuery = "SELECT a.ITEMNMBR, a.ITEMDESC, a.ITMSHNAM, a.SELNGUOM, b.QTYONHND, c.LISTPRCE " +
                                  "FROM IV00101 a " +
                                  "INNER JOIN IV00102 b ON a.ITEMNMBR = b.ITEMNMBR " +
                                  "INNER JOIN IV00105 c ON a.ITEMNMBR = c.ITEMNMBR " +
                                  "WHERE b.RCRDTYPE = 1 AND a.ITEMNMBR LIKE '" + partialItemID.Trim() + "%'";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader dr = sqlCmd.ExecuteReader();
                DataTable dt = new DataTable("Item");
                dt.Load(dr);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                dt.WriteXml(xmlWriter);

                dr.Close();
                dbConn.Close();

                string itemListXML = xmlWriter.ToString();
                string itemListTransformedXML = Utils.TransformXML(itemListXML, "ItemList.xslt");
                return itemListTransformedXML;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        // Method Name: GetUOM
        // Assigned To: Brian 
        // Query: SELECT ITEMNMBR, UOFM, UOMPRICE FROM IV00108 WHERE ITEMNMBR = <itemID>
        [WebMethod]
        public string GetUOM(string loginID, string password, string itemID)
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection(loginID, password);
                 
                string sqlQuery = "SELECT ITEMNMBR, UOFM, UOMPRICE FROM IV00108 WHERE ITEMNMBR = '" + itemID.Trim() + "'";

                SqlCommand sqlCmd = new SqlCommand(sqlQuery, dbConn);
                SqlDataReader dr = sqlCmd.ExecuteReader();
                DataTable dt = new DataTable("UOM");
                dt.Load(dr);

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter();
                dt.WriteXml(xmlWriter);

                dr.Close();
                dbConn.Close();

                string uomXML = xmlWriter.ToString();
                return Utils.TransformXML(uomXML, "UOMList.xslt");
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        // Method Name: GetCardTypes
        // Assigned To: James
        // Query: SELECT CARDNAME FROM SY03100

        [WebMethod]
        public string GetCardTypes( string loginID, string password )
        {
            try
            {
                SqlConnection dbConn = Utils.OpenDBConnection( loginID, password );

                string sqlQuery = "SELECT CARDNAME FROM SY03100";

                SqlCommand sqlCmd = new SqlCommand( sqlQuery, dbConn );
                SqlDataReader cardTypeDR = sqlCmd.ExecuteReader( );
                DataTable cardTypeDT = new DataTable( "CardType" );
                cardTypeDT.Load( cardTypeDR );

                System.IO.StringWriter xmlWriter = new System.IO.StringWriter( );
                cardTypeDT.WriteXml( xmlWriter );

                cardTypeDR.Close( );
                dbConn.Close( );

                string cardTypeListXML = xmlWriter.ToString( );
                string cardTypeListTransformedXML = Utils.TransformXML( cardTypeListXML, "CardTypeList.xslt" );

                return cardTypeListTransformedXML;

            }
            catch( Exception ex )
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetDocTypes(string loginID, string password)
        {
            try
            {
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data");
                xmlPath += "\\" + "DocTypes.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetPaymentTypes(string loginID, string password, string docType)
        {
            try
            {
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data");
                xmlPath += "\\" + "PaymentTypes.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                return xmlDoc.OuterXml;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetNewDocID(string loginID, string password, string docType)
        {
            try
            {
                string docTypeID = string.Empty;
                XmlDocument xmlDoc = LoadDocTypes();
                XmlNode xmlDocTypeName = xmlDoc.DocumentElement.SelectSingleNode("DocType/Name[. = \"" + docType.Trim() + "\"]");
                if (xmlDocTypeName != null)
                {
                    // Translate DocType from string to int
                    XmlNode xmlDocTypeEnum = xmlDocTypeName.ParentNode.SelectSingleNode("ID");
                    if (xmlDocTypeEnum != null)
                    {
                        docType = xmlDocTypeEnum.InnerText;
                    }
                    // Get associated DocTypeID
                    XmlNode xmlDocTypeID = xmlDocTypeName.ParentNode.SelectSingleNode("TypeID");
                    if (xmlDocTypeID != null)
                    {
                        docTypeID = xmlDocTypeID.InnerText;
                    }
                }
                else
                {
                    docType = "0";
                }

                string connString = ConfigurationManager.ConnectionStrings["GPDatabase_ECONNECT"].ConnectionString;

                GetSopNumber sopNumber = new GetSopNumber();
                string newDocID = sopNumber.GetNextSopNumber(int.Parse(docType), docTypeID, connString);

                return newDocID.Trim();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetNewCustomerID(string loginID, string password)
        {
            try
            {
                string ticks = DateTime.Now.Ticks.ToString();
                return "MOB" + ticks.Substring(ticks.Length - 9);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetDefaultSiteID(string loginID, string password)
        {
            try
            {
                string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data");
                xmlPath += "\\" + "SOPDefaults.xml";

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);

                string siteID = xmlDoc.DocumentElement.SelectSingleNode("SiteID").InnerText;
                return siteID;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetErrorMessage()
        {
            try
            {
                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        #endregion :: Public Methods ::

        #region :: Private Methods ::

        private string TranslateDocType(string docType)
        {
            string docTypeID = "0";
            XmlDocument xmlDoc = LoadDocTypes();
            XmlNode xmlDocTypeName = xmlDoc.DocumentElement.SelectSingleNode("DocType/Name[. = \"" + docType.Trim() + "\"]");
            if (xmlDocTypeName != null)
            {
                XmlNode xmlDocTypeID = xmlDocTypeName.ParentNode.SelectSingleNode("ID");
                if (xmlDocTypeID != null)
                {
                    docTypeID = xmlDocTypeID.InnerText;
                }
            }
            return docTypeID;
        }

        private XmlDocument LoadDocTypes()
        {
            string xmlPath = HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data");
            xmlPath += "\\DocTypes.xml";

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(xmlPath);

            return xmlDoc;
        }

        #endregion :: Private Methods ::
    }
}